﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
public partial class TestDB : System.Web.UI.Page
{
    public static readonly string CONN_STRING = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GetData();
        }
    }

    public void GetData()
    {
        int id = 1;
        int top = 10;
        int indexTop = AspNetPager1.CurrentPageIndex * top;
        string sql = string.Format(@"SELECT TOP {0} *
                                        FROM TestTable
                                        WHERE ID NOT IN
                                        (SELECT TOP {1} id
                                        FROM TestTable
                                        ORDER BY id)
                                        ORDER BY ID", top, indexTop);
        SqlParameter[] sqlParameters = new[]
            {
                new SqlParameter("@indexTop", indexTop),
                new SqlParameter("@top",top)
            };
        DataSet ds = new DataSet();
        using (SqlConnection conn = new SqlConnection(CONN_STRING))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql);
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            //foreach (var sqlParameter in sqlParameters)
            //{
            //    cmd.Parameters.Add(sqlParameter);
            //}

            //object dataTable = cmd.ExecuteScalar();
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);

            sqlDataAdapter.Fill(ds);
            cmd.Parameters.Clear();
        }
        AspNetPager1.RecordCount = ds.Tables[0].Rows.Count;
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }

    protected void AspNetPager1_PageChanged(object sender, EventArgs e)
    {
        GetData();
    }
}